-- This demo shows the effect on case- and accent-sensitivity, both on data
-- and metadata.
-- © Erland Sommarskog 2025.

-- Start with a case-insensitive database.
USE Latin1_General_100_CI_AS
go
-- Plain string comparison.
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
go

-- What is you server collation? When I do this demo it is case-sensitive, but
-- your server may be different.
SELECT serverproperty('Collation')
go
-- This will succeed if your collation is case-insensitive.
DECLARE @a int = 12
SELECT @A
go
-- And in that case, this will fail. Else this illustrates that data in 
-- variables always follow the database collation.
DECLARE @a char(3) = 'aaa',
        @A char(3) = 'AAA'
SELECT IIF(@a = @A, 'Equal', 'Not equal')
go

-- Create a table with mixed case and columns in lower-case, then access it
-- with different case.
CREATE TABLE SomeTable (Id int, cString varchar(20))
go
SELECT ID, cstring FROM sometable
go
-- This fails! Nothing strange, if you come from Pascal, Basic etc.
CREATE TABLE SOMETABLE (a varchar(20), b date)
go   


-- Now create a database with a case-sensitive collation and move there.
CREATE DATABASE Latin1_General_100_CS_AS COLLATE Latin1_General_100_CS_AS
go
USE Latin1_General_100_CS_AS
go
-- Same tests as above - but outcome is different.
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
go
CREATE TABLE SomeTable (Id int, cString numeric(20,2))
go
SELECT ID, cstring FROM sometable
go
SELECT ID, cstring FROM SomeTable
go
CREATE TABLE SOMETABLE (a varchar(20), b date)
-- The outcome for the table and column names are perfectly normal if you
-- come from Python, Perl or C#.
go

-- Let's try a case-insensitive database for Hungarian.
CREATE DATABASE Hungarian_CI_AS COLLATE Hungarian_CI_AS
go
USE Hungarian_CI_AS
go
-- So far so good....
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
CREATE TABLE SomeTable (Id int, cString varchar(20))
go
-- But this fails! As we have learnt, CS counts a letter of its own in 
-- Hungarian, so "cs" is that letter. But cS is "c" + "S".
SELECT ID, cstring FROM sometable
go
-- Now create a case-insensitive database for Turkish.
CREATE DATABASE Turkish_CI_AS COLLATE Turkish_CI_AS
go
USE Turkish_CI_AS
go
-- The result is surprising.
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
go
-- Look! The dot on the lower-case i has moved!
SELECT upper(N'insert'), lower(N'INSERT')
go
-- Create a table with an uppercase name and access it in lowercase. Oops!
CREATE TABLE Instruments (a int NOT NULL)
go
SELECT a FROM instruments
go
-- Lesson: when developing for an international market, go with a case-sensitive
-- collation, both on server and database level - or forget the Turkish market.

-- Now create an accent-insensitive database and move there.
CREATE DATABASE Finnish_Swedish_CI_AI COLLATE Finnish_Swedish_CI_AI
go
USE Finnish_Swedish_CI_AI
go
-- Create a table that accepts a pair of words, with comparison columns 
-- according to English, Croatian and Swedish.
CREATE TABLE AccentInsensitive(
             leftword  nvarchar(20) NOT NULL,
             rightword nvarchar(20) NOT NULL,
             English AS IIF(leftword  COLLATE Latin1_General_CI_AI = 
                           rightword COLLATE Latin1_General_CI_AI, 
                           'Equal', 'Not equal'),
             Croatian  AS IIF(leftword  COLLATE Croatian_CI_AI =
                              rightword COLLATE Croatian_CI_AI,
                              'Equal', 'Not Equal'),
             Swedish AS IIF(leftword = rightword, 'Equal', 'Not equal')
)

-- Add some words.
INSERT AccentInsensitive(leftword, rightword)
   VALUES (N'resume',    N'résumé'),     -- Two different English words.
          (N'ćevapčići', N'cevapcici'),  -- A Croatian word, properly spelled to the left, mutilated to the right.
          (N'cooperate', N'coöperate'),  -- Two alternate spellings of the same English word - but one glyph happens to agree with a character in Swedish.
          (N'wine',      N'vine'),       -- Two different English words.
          (N'Müller',    N'myller')      -- A name and Swedish for "throng".

SELECT * FROM AccentInsensitive
go
